/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package study.alex.db.auto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Alex
 */
public class AddInfo {
    private int nextIdOwners = 1;
    private int nextIdAuto = 1;

    private void addRecords() throws SQLException {
        for (int i = 0; i < data.length; i++) addRecord(data[i].name, data[i].address, data[i].model, data[i].regNum);
    }

    private void addRecord(String name, String address, String model, String regNum) throws SQLException {
        
        
        ResultSet res = st.executeQuery("SELECT MAX(id) AS ID FROM Owners");
        res.next();
        
        int nextIdOwners = res.getInt("ID");
//        int nextIdOwners = 0;
        
        nextIdOwners += 1;
        
        res = st.executeQuery("SELECT MAX(id) AS ID FROM Auto");
        res.next();
        int nextIdAuto = res.getInt("ID");
        
//          int nextIdAuto = 0;
        
        
        nextIdAuto += 1;
        
        
        
        String q1 = "INSERT INTO Owners VALUES (" + nextIdOwners + ", \'" + name + "\', \'" + address + "\')";
        String q2 = "INSERT INTO Auto VALUES (" + nextIdAuto + ", \'" + model + "\', \'" + regNum + "\', " + nextIdOwners +")";
        
        System.out.println(q1);
        System.out.println(q2);
        
        
        st.executeUpdate(q1);
        st.executeUpdate(q2);
        conn.commit();
        nextIdOwners++;
        nextIdAuto++;
        
    }

    private void ConnectDB(String url) throws SQLException {
        
        conn = DriverManager.getConnection(url, "admin", "admin");
        conn.setAutoCommit(false);
//        st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        st = conn.createStatement();
    }

    private void CloseDB() throws SQLException {
        if (st != null) st.close();
        if (!conn.isClosed()) conn.close();
    }


    private class Data {
        String name;
        String address;
        String model;
        String regNum;

        public Data(String name, String address, String model, String regNum) {
            this.name = name;
            this.address = address;
            this.model = model;
            this.regNum = regNum;
        }
    }
    private Connection conn;
    private Statement st;

    private Data[] data =   {new Data("Ivanov2", "Spb", "Audi", "o712cc98rus2"),
                                new Data("Petrov2", "Spb", "BMV", "o234kc78rus2"),
                                  new Data("Sidorov2", "Moscow", "Gaz", "p542am77rus2"),
                                  new Data("Egorov2", "Moscow", "ZAZ", "k456ca77rus2")
                            };
    
    
    public static void main(String[] args) {

        AddInfo addinfo = new AddInfo();
        
        try {
            addinfo.ConnectDB("jdbc:derby://localhost:1527/test");
            addinfo.addRecords();
            addinfo.CloseDB();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }        
    
}
